
-- Example:
/*
DROP VIEW matview;
CREATE VIEW matview as 

SELECT sum(salinity) FROM cruise.castobservation;



create table matview_temp as (select * from integrated.platform)

select scipg.ismaterialized('public', 'matview');
select scipg.definition('public', 'matview');

select scipg.materialize('public', 'matview');
select scipg.dematerialize('public', 'matview');
select scipg.rematerialize('public', 'matview');

select * from matview;
*/


CREATE TABLE scipg.materializedviews
(
  schemaname text NOT NULL,
  viewname text NOT NULL,
  refresh_interval interval NOT NULL DEFAULT '1 year'::interval,
  last_refreshed timestamp with time zone NOT NULL DEFAULT '1900-01-01 00:00:00-08'::timestamp with time zone,
  definition text NOT NULL,
  CONSTRAINT materializedviews_pkey PRIMARY KEY (schemaname, viewname)
) ;
COMMENT ON TABLE scipg.materializedviews IS 'Table to manage definitions of materialized views.  Users do not need to query or update this table directly.';


CREATE OR REPLACE FUNCTION scipg.ismaterialized(schemaname text, viewname text)
  RETURNS boolean AS
$BODY$

SELECT EXISTS (
SELECT schemaname, viewname, definition 
  FROM scipg.materializedviews
 WHERE schemaname = $1 and viewname = $2
 )

$BODY$
  LANGUAGE 'sql' VOLATILE;


CREATE OR REPLACE FUNCTION scipg.definition(schemaname text, viewname text)
  RETURNS text AS
$BODY$

SELECT COALESCE(
(SELECT definition 
  FROM scipg.materializedviews
 WHERE schemaname = $1 and viewname = $2)
, (SELECT definition
  FROM pg_views v
 WHERE viewname = $2
   AND schemaname = $1)
)
$BODY$
LANGUAGE 'sql' VOLATILE;
  


CREATE OR REPLACE FUNCTION scipg.rematerialize(schemaname text, viewname text)
  RETURNS boolean AS
$BODY$
DECLARE
qualifiedviewname text;
cachetable text;
definition text;

BEGIN

qualifiedviewname := schemaname || '.' || viewname;
cachetable := qualifiedviewname || '_cache';

EXECUTE 'SELECT scipg.definition(''' || $1 || ''', ''' ||  $2 || ''')' INTO definition;

IF definition IS NULL THEN
  RAISE EXCEPTION 'View % does not exist.',  qualifiedviewname;
END IF;

-- Create the table to hold the materialized results
EXECUTE 'CREATE TABLE ' || cachetable || '_temp AS ' || definition;

BEGIN
-- Rename existing materialized table
EXECUTE 'ALTER TABLE ' || cachetable || ' RENAME TO ' || viewname || '_delete';
EXCEPTION
  WHEN 	undefined_table THEN
     RAISE EXCEPTION 'View % is not materialized ( % not found). Try SELECT scipg.materialize(''%'', ''%'')', qualifiedviewname, cachetable, $1, $2;
END;

-- Rename newly created table 
EXECUTE 'ALTER TABLE ' || cachetable || '_temp RENAME TO ' || viewname || '_cache';

EXECUTE $$ 
CREATE OR REPLACE RULE "_RETURN" AS
   ON SELECT TO $$  || qualifiedviewname || $$
   DO INSTEAD
SELECT * FROM $$ || cachetable;

-- Delete old table
EXECUTE 'DROP TABLE ' || viewname || '_delete';

RETURN True;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;







CREATE OR REPLACE FUNCTION scipg.materialize(schemaname text, viewname text)
  RETURNS boolean AS
$BODY$
DECLARE
qualifiedviewname text;
cachetable text;
definition text;
ismaterialized text;
quoteddefinition text;

BEGIN

qualifiedviewname := schemaname || '.' || viewname;
cachetable := qualifiedviewname || '_cache';

EXECUTE 'SELECT scipg.definition(''' || $1 || ''', ''' ||  $2 || ''')' INTO definition;

IF definition IS NULL THEN
  RAISE EXCEPTION 'View % does not exist.',  qualifiedviewname;
END IF;

EXECUTE 'SELECT scipg.ismaterialized(''' || $1 || ''', ''' ||  $2 || ''')' INTO ismaterialized;

IF ismaterialized THEN 

  RAISE EXCEPTION 'View % is already materialized.  Use SELECT scipg.rematerialize(''%'',''%'') to refresh.', qualifiedviewname, $1, $2;
  -- Reset the _RETURN rule on the view to its original value

ELSE

  -- Insert the view into the list of materialized views
  SELECT replace(definition, $$'$$, $$''$$) INTO quoteddefinition ;  --'
  
  EXECUTE 'INSERT INTO scipg.materializedviews (schemaname, viewname,definition)
VALUES (''' || $1 || ''', ''' || $2 || ''', ''' || quoteddefinition ||''')';

  -- Create the table to hold the materialized results
  EXECUTE 'CREATE TABLE ' || cachetable || ' AS ' || definition;

  -- Set the _RETURN rule to rewrite selects on the view to selects on the cache
  EXECUTE $$ 
  CREATE OR REPLACE RULE "_RETURN" AS
     ON SELECT TO $$ || qualifiedviewname || $$ 
     DO INSTEAD 
     SELECT * FROM $$ || cachetable;

  -- Finally, update the last_refreshed time on the view
  EXECUTE 'UPDATE scipg.materializedviews SET last_refreshed = now()
WHERE schemaname = ''' || $1 || ''' AND viewname = ''' || $2 || '''';

  RETURN True;
END IF;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
















CREATE OR REPLACE FUNCTION scipg.dematerialize(schemaname text, viewname text)
  RETURNS boolean AS
$BODY$
DECLARE
qualifiedviewname text;
cachetable text;
definition text;
ismaterialized boolean;
quoteddefinition text;
cache_exists boolean;
view_exists boolean;
BEGIN

qualifiedviewname := schemaname || '.' || viewname;
cachetable := qualifiedviewname || '_cache';

EXECUTE 'SELECT scipg.definition(''' || $1 || ''', ''' ||  $2 || ''')' INTO definition;
EXECUTE 'SELECT scipg.tableexists('''||$1 ||''','''||$2||'_cache'')' INTO cache_exists;
EXECUTE 'SELECT scipg.tableexists('''||$1 ||''','''||$2||''')' INTO view_exists;
EXECUTE 'SELECT scipg.ismaterialized('''||$1 ||''','''||$2||''')' INTO ismaterialized;

IF definition IS NULL THEN
  RAISE NOTICE 'View % does not exist.  Cleaning up cache if it exists.', qualifiedviewname;
END IF;

IF ismaterialized AND view_exists AND definition IS NOT NULL THEN

  -- Reset the rule back to the original view definition
  BEGIN
  EXECUTE $$ 
  CREATE OR REPLACE RULE "_RETURN" AS
     ON SELECT TO $$ || qualifiedviewname || $$ 
     DO INSTEAD $$ ||
     definition;
  EXCEPTION 
    WHEN invalid_object_definition THEN
    RAISE NOTICE 'Unable to remap view.  View type and original view definition may not match.  Deleting materialization record.';
  END;  
    
END IF;

IF ismaterialized THEN
  -- Delete the tuple representing the materialized view      
  EXECUTE $$ DELETE FROM scipg.materializedviews
WHERE schemaname = '$$ || $1 || $$' and viewname = '$$ || $2 || $$' $$;
END IF;


-- Remove cache table, if it still exists
IF cache_exists THEN 
   RAISE NOTICE 'Deleting cache table.';
   EXECUTE 'DROP TABLE ' || cachetable;
END IF;  

RETURN True;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;






COMMENT ON FUNCTION scipg.materialize(text, text) IS 'If schemaname.viewname exists, (re-)create a table schemname.viewname_cache and fill it with the content of the view.  
Then, create a rule on viewname that redirects select statements to the cache and insert a tuple into scipg.materializedviews if one was not already present.

Note that this function also refreshes a materialized view if one was present.

Returns True if the view was not already materialized and False otherwise.';


COMMENT ON FUNCTION scipg.dematerialize(text, text) IS 'Idempotently remove cache and reset view to original definition.';

COMMENT ON FUNCTION scipg.rematerialize(text, text) IS 'Non-blocking cache refresh.';


